Load Movie Data from MySQL
res <- dbSendQuery(mydb, "SELECT * FROM ratings")
mRatings <- fetch(res, n=-1)
# Disconnect from the database
dbDisconnect(mydb)
## [1] TRUE
View Data
# View Data
knitr::kable(mRatings)
| Ann Doe |
Baar Baar Dekho |
2016 |
Romance |
4 |
| Ann Doe |
Bad Moms |
2016 |
Comedy |
4 |
| Ann Doe |
Ben-Hur |
2016 |
Action |
3 |
| Ann Doe |
Ghostbusters |
2016 |
Comedy |
3 |
| Ann Doe |
Jason Bourne |
2016 |
Thriller |
4 |
| Ann Doe |
Morgan |
2016 |
Horror |
2 |
| Jane Doe |
Baar Baar Dekho |
2016 |
Romance |
2 |
| Jane Doe |
Bad Moms |
2016 |
Comedy |
5 |
| Jane Doe |
Ben-Hur |
2016 |
Action |
1 |
| Jane Doe |
Ghostbusters |
2016 |
Comedy |
2 |
| Jane Doe |
Jason Bourne |
2016 |
Thriller |
3 |
| Jane Doe |
Morgan |
2016 |
Horror |
1 |
| John Doe |
Baar Baar Dekho |
2016 |
Romance |
3 |
| John Doe |
Bad Moms |
2016 |
Comedy |
2 |
| John Doe |
Ben-Hur |
2016 |
Action |
3 |
| John Doe |
Ghostbusters |
2016 |
Comedy |
2 |
| John Doe |
Jason Bourne |
2016 |
Thriller |
5 |
| John Doe |
Morgan |
2016 |
Horror |
1 |
| Mike Doe |
Baar Baar Dekho |
2016 |
Romance |
4 |
| Mike Doe |
Bad Moms |
2016 |
Comedy |
3 |
| Mike Doe |
Ben-Hur |
2016 |
Action |
2 |
| Mike Doe |
Ghostbusters |
2016 |
Comedy |
2 |
| Mike Doe |
Jason Bourne |
2016 |
Thriller |
4 |
| Mike Doe |
Morgan |
2016 |
Horror |
1 |
| Neil Doe |
Baar Baar Dekho |
2016 |
Romance |
3 |
| Neil Doe |
Bad Moms |
2016 |
Comedy |
3 |
| Neil Doe |
Ben-Hur |
2016 |
Action |
4 |
| Neil Doe |
Ghostbusters |
2016 |
Comedy |
2 |
| Neil Doe |
Jason Bourne |
2016 |
Thriller |
4 |
| Neil Doe |
Morgan |
2016 |
Horror |
4 |
Get Movie Names
Get Unique Movie names
MovieName
Baar Baar Dekho Bad Moms
Ben-Hur
Ghostbusters
Jason Bourne
Morgan
movieList1 = list()
movieList2 = list()
for (i in 1:6) {
movieName <- mNames$MovieName[i]
meanRat <- subset(mRatings, MovieName == movieName, select = c(Rating))
mURL <- paste0("http://www.omdbapi.com/?y=2016&plot=short&r=json&t=",movieName)
raw.data <- readLines(mURL, warn = "F")
rd <- fromJSON(raw.data)
nData <- data.frame(movieName, mean(meanRat$Rating))
newData <- data.frame(rd$Title,mean(meanRat$Rating), rd$Rated, rd$Released, rd$Director, rd$Actors, rd$imdbRating )
newData$Poster <- sprintf('')
movieList1[[i]] <- nData
movieList2[[i]] <- newData
}
mNameRat1 <- bind_rows(movieList1)
colnames(mNameRat1) <- c("Title", "Mean User Rating (1-5) ")
knitr::kable(mNameRat1)
| Baar Baar Dekho |
3.2 |
| Bad Moms |
3.4 |
| Ben-Hur |
2.6 |
| Ghostbusters |
2.2 |
| Jason Bourne |
4.0 |
| Morgan |
1.8 |
mNameRat2 <- bind_rows(movieList2)
colnames(mNameRat2) <- c("Title", "User Rating", "Rated", "Released", "Director","Cast","IMDB Rating","Poster")
knitr::kable(mNameRat2)
| Baar Baar Dekho |
3.2 |
N/A |
09 Sep 2016 |
Nitya Mehra |
Sidharth Malhotra, Katrina Kaif, Sayani Gupta, Rajit Kapoor |
7.7 |
 |
| Bad Moms |
3.4 |
R |
29 Jul 2016 |
Jon Lucas, Scott Moore |
Mila Kunis, Kathryn Hahn, Kristen Bell, Christina Applegate |
6.7 |
 |
| Ben-Hur |
2.6 |
PG-13 |
19 Aug 2016 |
Timur Bekmambetov |
Jack Huston, Toby Kebbell, Rodrigo Santoro, Nazanin Boniadi |
5.7 |
 |
| Ghostbusters |
2.2 |
PG-13 |
15 Jul 2016 |
Paul Feig |
Zach Woods, Kristen Wiig, Ed Begley Jr., Charles Dance |
5.5 |
 |
| Jason Bourne |
4.0 |
PG-13 |
29 Jul 2016 |
Paul Greengrass |
Matt Damon, Tommy Lee Jones, Alicia Vikander, Vincent Cassel |
7.0 |
 |
| Morgan |
1.8 |
R |
02 Sep 2016 |
Luke Scott |
Kate Mara, Rose Leslie, Jennifer Jason Leigh, Anya Taylor-Joy |
N/A |
 |